<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE dita PUBLIC "-//OASIS//DTD DITA Composite//EN" "ditabase.dtd">
<dita>
  <topic id="topic1" xml:lang="en">
    <title id="if135496">Data Types</title>
    <body>
      <p>Greenplum Database has a rich set of native data types available to users. Users may also
        define new data types using the <codeph>CREATE TYPE</codeph> command. This reference shows
        all of the built-in data types. In addition to the types listed here, there are also some
        internally used data types, such as <i>oid</i> (object identifier), but those are not
        documented in this guide. </p>
      <p>Additional modules that you register may also install new data
        types. The <codeph>hstore</codeph> module, for example, introduces a new data type and
        associated functions for working with key-value pairs. See <xref
          href="modules/hstore.xml"/>. The <codeph>citext</codeph> module
        adds a case-insensitive text data type. See <xref href="modules/citext.xml"/>.</p>
      <p>The following data types are specified by SQL: <i>bit</i>, <i>bit varying</i>,
          <i>boolean</i>, <i>character varying, varchar</i>, <i>character, char</i>, <i>date</i>,
          <i>double precision</i>, <i>integer</i>, <i>interval</i>, <i>numeric</i>, <i>decimal</i>,
          <i>real</i>, <i>smallint</i>, <i>time</i> (with or without time zone), and
          <i>timestamp</i> (with or without time zone). </p>
      <p>Each data type has an external representation determined by its input and output functions.
        Many of the built-in types have obvious external formats. However, several types are either
        unique to PostgreSQL (and Greenplum Database), such as geometric paths, or have several
        possibilities for formats, such as the date and time types. Some of the input and output
        functions are not invertible. That is, the result of an output function may lose accuracy
        when compared to the original input.</p>
      <table id="if139202">
        <title>Greenplum Database Built-in Data Types</title>
        <tgroup cols="5">
          <colspec colnum="1" colname="col1" colwidth="95pt"/>
          <colspec colnum="2" colname="col2" colwidth="74pt"/>
          <colspec colnum="3" colname="col3" colwidth="46pt"/>
          <colspec colnum="4" colname="col4" colwidth="101pt"/>
          <colspec colnum="5" colname="col5" colwidth="133pt"/>
          <thead>
            <row>
              <entry colname="col1">Name</entry>
              <entry colname="col2">Alias</entry>
              <entry colname="col3">Size</entry>
              <entry colname="col4">Range</entry>
              <entry colname="col5">Description</entry>
            </row>
          </thead>
          <tbody>
            <row>
              <entry colname="col1">bigint</entry>
              <entry colname="col2">int8</entry>
              <entry colname="col3">8 bytes</entry>
              <entry colname="col4">-922337203&#x200b;6854775808 to
                922337203&#x200b;6854775807</entry>
              <entry colname="col5">large range integer</entry>
            </row>
            <row>
              <entry colname="col1">bigserial</entry>
              <entry colname="col2">serial8</entry>
              <entry colname="col3">8 bytes</entry>
              <entry colname="col4">1 to 922337203&#x200b;6854775807</entry>
              <entry colname="col5">large autoincrementing integer</entry>
            </row>
            <row>
              <entry colname="col1">bit [ (n) ]<fn id="if139219">For variable length data types, if
                  the data is greater than or equal to 127 bytes, the storage overhead is 4 bytes
                  instead of 1.</fn></entry>
              <entry colname="col2"/>
              <entry colname="col3"><i>n</i> bits</entry>
              <entry colname="col4"><xref
                  href="https://www.postgresql.org/docs/9.4/sql-syntax.html#SQL-SYNTAX-BIT-STRINGS"
                  scope="external" format="html">bit string constant</xref></entry>
              <entry colname="col5">fixed-length bit string</entry>
            </row>
            <row>
              <entry colname="col1">bit varying [ (n) ]<xref href="#topic1/if139219" scope="local"
                  type="fn" format="dita"/></entry>
              <entry colname="col2">varbit</entry>
              <entry colname="col3">actual number of bits</entry>
              <entry colname="col4"><xref
                  href="https://www.postgresql.org/docs/9.4/sql-syntax.html#SQL-SYNTAX-BIT-STRINGS"
                  scope="external" format="html">bit string constant</xref></entry>
              <entry colname="col5">variable-length bit string</entry>
            </row>
            <row>
              <entry colname="col1">boolean</entry>
              <entry colname="col2">bool</entry>
              <entry colname="col3">1 byte</entry>
              <entry colname="col4">true/false, t/f, yes/no, y/n, 1/0</entry>
              <entry colname="col5">logical boolean (true/false)</entry>
            </row>
            <row>
              <entry colname="col1">box</entry>
              <entry colname="col2"/>
              <entry colname="col3">32 bytes</entry>
              <entry colname="col4">((x1,y1),(x2,y2))</entry>
              <entry colname="col5">rectangular box in the plane - not allowed in distribution key
                columns.</entry>
            </row>
            <row>
              <entry colname="col1">bytea<xref href="#topic1/if139219" scope="local" type="fn"
                  format="dita"/></entry>
              <entry colname="col2"/>
              <entry colname="col3">1 byte + <i>binary string</i></entry>
              <entry colname="col4">sequence of <xref
                  href="https://www.postgresql.org/docs/9.4/datatype-binary.html#DATATYPE-BINARY-SQLESC"
                  scope="external" format="html"><ph>octets</ph></xref></entry>
              <entry colname="col5">variable-length binary string</entry>
            </row>
            <row>
              <entry colname="col1">character [ (n) ]<xref href="#topic1/if139219" scope="local"
                  type="fn" format="dita"/></entry>
              <entry colname="col2">char [ (n) ]</entry>
              <entry colname="col3">1 byte + <i>n</i></entry>
              <entry colname="col4">strings up to <i>n</i> characters in length</entry>
              <entry colname="col5">fixed-length, blank padded</entry>
            </row>
            <row>
              <entry colname="col1">character varying [ (n) ]<xref href="#topic1/if139219"
                  scope="local" type="fn" format="dita"/></entry>
              <entry colname="col2">varchar [ (n) ] </entry>
              <entry colname="col3">1 byte + <i>string size</i></entry>
              <entry colname="col4">strings up to <i>n</i> characters in length</entry>
              <entry colname="col5">variable-length with limit</entry>
            </row>
            <row>
              <entry colname="col1">cidr</entry>
              <entry colname="col2"/>
              <entry colname="col3">12 or 24 bytes</entry>
              <entry colname="col4"/>
              <entry colname="col5">IPv4 and IPv6 networks</entry>
            </row>
            <row>
              <entry colname="col1">circle</entry>
              <entry colname="col2"/>
              <entry colname="col3">24 bytes</entry>
              <entry colname="col4">&lt;(x,y),r&gt; (center and radius)</entry>
              <entry colname="col5">circle in the plane - not allowed in distribution key
                columns.</entry>
            </row>
            <row>
              <entry colname="col1">date</entry>
              <entry colname="col2"/>
              <entry colname="col3">4 bytes</entry>
              <entry colname="col4">4713 BC - 294,277 AD </entry>
              <entry colname="col5">calendar date (year, month, day)</entry>
            </row>
            <row>
              <entry colname="col1">decimal [ (p, s) ]<xref href="#topic1/if139219" scope="local"
                  type="fn" format="dita"/></entry>
              <entry colname="col2">numeric [ (p, s) ]</entry>
              <entry colname="col3">variable</entry>
              <entry colname="col4">no limit</entry>
              <entry colname="col5">user-specified precision, exact</entry>
            </row>
            <row>
              <entry colname="col1">double precision</entry>
              <entry colname="col2">float8<p>float</p></entry>
              <entry colname="col3">8 bytes</entry>
              <entry colname="col4">15 decimal digits precision</entry>
              <entry colname="col5">variable-precision, inexact</entry>
            </row>
            <row>
              <entry colname="col1">inet</entry>
              <entry colname="col2"/>
              <entry colname="col3">12 or 24 bytes</entry>
              <entry colname="col4"/>
              <entry colname="col5">IPv4 and IPv6 hosts and networks</entry>
            </row>
            <row>
              <entry colname="col1">integer</entry>
              <entry colname="col2">int, int4</entry>
              <entry colname="col3">4 bytes</entry>
              <entry colname="col4">-2147483648 to +2147483647</entry>
              <entry colname="col5">usual choice for integer</entry>
            </row>
            <row>
              <entry colname="col1">interval [ <varname>fields</varname> ] [ (p) ]</entry>
              <entry colname="col2"/>
              <entry colname="col3">16 bytes</entry>
              <entry colname="col4">-178000000 years to 178000000 years</entry>
              <entry colname="col5">time span</entry>
            </row>
            <row>
              <entry colname="col1">json</entry>
              <entry colname="col2"/>
              <entry colname="col3">1 byte + json size</entry>
              <entry colname="col4">json of any length</entry>
              <entry colname="col5">variable unlimited length</entry>
            </row>
            <row>
              <entry colname="col1">jsonb</entry>
              <entry colname="col2"/>
              <entry colname="col3">1 byte + binary string</entry>
              <entry colname="col4">json of any length in a decomposed binary format</entry>
              <entry colname="col5">variable unlimited length</entry>
            </row>
            <row>
              <entry colname="col1">lseg</entry>
              <entry colname="col2"/>
              <entry colname="col3">32 bytes</entry>
              <entry colname="col4">((x1,y1),(x2,y2))</entry>
              <entry colname="col5">line segment in the plane - not allowed in distribution key
                columns.</entry>
            </row>
            <row>
              <entry colname="col1">macaddr</entry>
              <entry colname="col2"/>
              <entry colname="col3">6 bytes</entry>
              <entry colname="col4"/>
              <entry colname="col5">MAC addresses</entry>
            </row>
            <row>
              <entry colname="col1">money</entry>
              <entry colname="col2"/>
              <entry colname="col3">8 bytes</entry>
              <entry colname="col4">-92233720368547758.08 to +92233720368547758.07</entry>
              <entry colname="col5">currency amount</entry>
            </row>
            <row>
              <entry colname="col1">path<xref href="#topic1/if139219" scope="local" type="fn"
                  format="dita"/></entry>
              <entry colname="col2"/>
              <entry colname="col3">16+16n bytes</entry>
              <entry colname="col4">[(x1,y1),...]</entry>
              <entry colname="col5">geometric path in the plane - not allowed in distribution key
                columns.</entry>
            </row>
            <row>
              <entry colname="col1">point</entry>
              <entry colname="col2"/>
              <entry colname="col3">16 bytes</entry>
              <entry colname="col4">(x,y)</entry>
              <entry colname="col5">geometric point in the plane - not allowed in distribution key
                columns.</entry>
            </row>
            <row>
              <entry colname="col1">polygon</entry>
              <entry colname="col2"/>
              <entry colname="col3">40+16n bytes</entry>
              <entry colname="col4">((x1,y1),...)</entry>
              <entry colname="col5">closed geometric path in the plane - not allowed in distribution
                key columns.</entry>
            </row>
            <row>
              <entry colname="col1">real</entry>
              <entry colname="col2">float4</entry>
              <entry colname="col3">4 bytes</entry>
              <entry colname="col4">6 decimal digits precision</entry>
              <entry colname="col5">variable-precision, inexact</entry>
            </row>
            <row>
              <entry colname="col1">serial</entry>
              <entry colname="col2">serial4</entry>
              <entry colname="col3">4 bytes</entry>
              <entry colname="col4">1 to 2147483647</entry>
              <entry colname="col5">autoincrementing integer</entry>
            </row>
            <row>
              <entry colname="col1">smallint</entry>
              <entry colname="col2">int2</entry>
              <entry colname="col3">2 bytes</entry>
              <entry colname="col4">-32768 to +32767</entry>
              <entry colname="col5">small range integer</entry>
            </row>
            <row>
              <entry colname="col1">text<xref href="#topic1/if139219" scope="local" type="fn"
                  format="dita"/></entry>
              <entry colname="col2"/>
              <entry colname="col3">1 byte + <i>string size</i></entry>
              <entry colname="col4">strings of any length</entry>
              <entry colname="col5">variable unlimited length</entry>
            </row>
            <row>
              <entry colname="col1">time [ (p) ] [ without time zone ]</entry>
              <entry colname="col2"/>
              <entry colname="col3">8 bytes</entry>
              <entry colname="col4">00:00:00[.000000] - 24:00:00[.000000]</entry>
              <entry colname="col5">time of day only</entry>
            </row>
            <row>
              <entry colname="col1">time [ (p) ] with time zone</entry>
              <entry colname="col2">timetz</entry>
              <entry colname="col3">12 bytes</entry>
              <entry colname="col4">00:00:00+1359 - 24:00:00-1359</entry>
              <entry colname="col5">time of day only, with time zone</entry>
            </row>
            <row>
              <entry colname="col1">timestamp [ (p) ] [ without time zone ] </entry>
              <entry colname="col2"/>
              <entry colname="col3">8 bytes</entry>
              <entry colname="col4">4713 BC - 294,277 AD </entry>
              <entry colname="col5">both date and time</entry>
            </row>
            <row>
              <entry colname="col1">timestamp [ (p) ] with time zone</entry>
              <entry colname="col2">timestamptz</entry>
              <entry colname="col3">8 bytes</entry>
              <entry colname="col4">4713 BC - 294,277 AD </entry>
              <entry colname="col5">both date and time, with time zone</entry>
            </row>
            <row>
              <entry colname="col1">uuid</entry>
              <entry colname="col2"/>
              <entry colname="col3">16 bytes</entry>
              <entry colname="col4"/>
              <entry colname="col5">Universally Unique Identifiers according to RFC 4122, ISO/IEC
                9834-8:2005</entry>
            </row>
            <row>
              <entry colname="col1">xml<xref href="#topic1/if139219" scope="local" type="fn"
                  format="dita"/></entry>
              <entry colname="col2"/>
              <entry colname="col3">1 byte + <i>xml size</i></entry>
              <entry colname="col4">xml of any length</entry>
              <entry colname="col5">variable unlimited length</entry>
            </row>
            <row>
              <entry colname="col1">txid_snapshot</entry>
              <entry colname="col2"/>
              <entry colname="col3"/>
              <entry colname="col4"/>
              <entry colname="col5">user-level transaction ID snapshot</entry>
            </row>
          </tbody>
        </tgroup>
      </table>
    </body>
  </topic>
</dita>
